IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MatrimonyBanking')
DROP DATABASE MatrimonyBanking
GO

CREATE DATABASE MatrimonyBanking
GO

USE MatrimonyBanking
GO

CREATE TABLE Account
(
	AccountCount int identity(1,1),
	AccountNumber nvarchar(16) primary key,
	AccountName nvarchar(150) not null,
	[Password] nvarchar(50) not null,
	ExpirationMonth int not null,
	ExpirationYear int not null,
	CVV int not null,
	AccountType nvarchar(10) not null,
	Balance int not null,
	CreditLine int,
	BeginBalance int not null,
	[Status] bit not null
)
GO

CREATE TABLE AccountInfo
(
	AccountCount int identity(1,1),
	AccountNumber nvarchar(16) primary key,
	DateOfBirth date not null,
	Gender nvarchar(6) not null,
	[Address] nvarchar(150) not null,
	PhoneNumber nvarchar(11) not null,
	ZipCode int not null,
	IdentityCard nvarchar(9) not null,
	Email nvarchar(150),
	BankLocations nvarchar(150),
	DateCreated date not null,
	[Description] nvarchar(max)
)
GO

CREATE TABLE Partners
(
	AccountNumber nvarchar(16) primary key,
	PartnerName nvarchar(150) not null,
	[Address] nvarchar(150) not null,
	PhoneNumber nvarchar(11) not null,
	Fax nvarchar(11) not null,
	Email nvarchar(150),
	Website nvarchar(150),
	ImageUrl nvarchar(150),
	[Description] nvarchar(max),
	[Status] bit not null
)
GO

CREATE TABLE History
(
	HistoryID int identity(1,1) Primary key,
	AccountNumber nvarchar(16) not null,
	Withdraw int,
	Deposit int,
	FeeForService int,
	Balance int not null,
	TradingTime datetime not null,
	TradingPlaces nvarchar(150) not null,
	Purpose nvarchar(150) not null,
	Note nvarchar(max),
	[Status] bit not null
)
GO

ALTER TABLE AccountInfo
ADD CONSTRAINT FK_AccountInfo_Account
FOREIGN KEY (AccountNumber)
REFERENCES Account(AccountNumber),
CONSTRAINT FK_Partners_Account
FOREIGN KEY (AccountNumber)
REFERENCES Account(AccountNumber),
CONSTRAINT FK_History_Account
FOREIGN KEY (AccountNumber)
REFERENCES Account(AccountNumber)
GO

INSERT INTO Account VALUES ('1234567890123456', 'NGUYEN DINH THAO', '123456', 9, 2015, 123, 'Visa', 100000, 50000, 5, 1);
INSERT INTO Account VALUES ('0987654321123456', 'NGUYEN HOANG KHOA', '123456', 5, 2015, 456, 'Visa', 100000, 50000, 5, 1);
GO

INSERT INTO AccountInfo VALUES ('1234567890123456', '07/06/1990', 'Male', 'D2, Binh Thanh, Ho Chi Minh', '01227500512', 70000, '123456789', 'matrimony@hcm.com', 'Matrimony Bank Ho Chi Minh', '09/01/2012', '');
INSERT INTO AccountInfo VALUES ('0987654321123456', '05/08/1991', 'Male', 'D5, Binh Thanh, Ho Chi Minh', '09876543210', 70000, '987654321', 'hoangkhoa@gmail.com', 'Matrimony Bank Ho Chi Minh', '09/01/2012', '');
GO

INSERT INTO Partners VALUES ('1234567890123456','Matrimony', 'D2, Binh Thanh, Ho Chi Minh', '01227500512', '0812345678', 'matrimony@hcm.com', 'http://matrimony.com', '', 'Matrimony - HaIT Inc', 1);
GO